﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace outREACH
{
    public class DCuser
    {
        public string developingCountries { get; set; }
        public string developingCity { get; set; }
        public string nameOfUser { get; set; }
        public string userPhoneNo { get; set; }
        public string userEmail { get; set; }
        public string dcUsername { get; set; }
        public string dcPassword { get; set; }
        public string dcAddress { get; set; }
        public string dcZipcode { get; set; }



        public static List<DCuser> GetDevelopingCountries()
        {
            SqlConnection conn = null;
            List<DCuser> result = new List<DCuser>();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from DevelopingCountries";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    DCuser dc = new DCuser();
                    dc.developingCountries = (string)dr["developingCountries"];
                    dc.developingCity = (string)dr["developingCity"];
                    dc.nameOfUser = (string)dr["nameOfUser"];
                    dc.userPhoneNo = (string)dr["userPhoneNo"];
                    dc.userEmail = (string)dr["userEmail"];
                    dc.dcUsername = (string)dr["dcUsername"];
                    dc.dcPassword = (string)dr["dcPassword"];
                    dc.dcAddress = (string)dr["dcAddress"];
                    dc.dcZipcode = (string)dr["dcZipcode"];
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }


        public static DCuser GetName(string developingCountries)
        {
            SqlConnection conn = null;
            DCuser dc = new DCuser();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from DevelopingCountrieswhere devlopingCountries = @devlopingCountries";
                comm.Parameters.AddWithValue("@developingCountries", developingCountries);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    dc.developingCountries = (string)dr["developingCountries"];
                    dc.dcUsername = (string)dr["dcUsername"];

                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return dc;
        }

        public static void Insert(DCuser dc)
        {
            SqlConnection conn = null;
            int userID = 0;
            try
            {
                SqlCommand comm = new SqlCommand();
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO DevelopingCountries(developingCountries,developingCity,nameOfUser,userPhoneNo,userEmail,dcUsername,dcPassword,dcAddress,dcZipcode) VALUES (@developingCountries,@developingCity,@nameOfUser,@userPhoneNo,@userEmail,@dcUsername,@dcPassword,@dcAddress,@dcZipcode)";
                comm.Parameters.AddWithValue("@developingCountries", dc.developingCountries);
                comm.Parameters.AddWithValue("@developingCity", dc.developingCity);
                comm.Parameters.AddWithValue("@nameOfUser", dc.nameOfUser);
                comm.Parameters.AddWithValue("@userPhoneNo", dc.userPhoneNo);
                comm.Parameters.AddWithValue("@userEmail", dc.userEmail);
                comm.Parameters.AddWithValue("@dcUsername", dc.dcUsername);
                comm.Parameters.AddWithValue("@dcPassword", dc.dcPassword);
                comm.Parameters.AddWithValue("@dcAddress", dc.dcAddress);
                comm.Parameters.AddWithValue("@dcZipcode", dc.dcZipcode);


                int rowsAffected = comm.ExecuteNonQuery();
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                userID = int.Parse(dr["UserID"].ToString());
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void changePassword(string dcUsername, string dcPassword, string dcNewPass)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "update DevelopingCountryUser set dcPassword = @dcNewPass where dcUsername = @dcUsername, dcPassword = @dcPassword";
                comm.Parameters.AddWithValue("@dcUsername", dcUsername);
                comm.Parameters.AddWithValue("@dcPassword", dcPassword);
                comm.Parameters.AddWithValue("@dcNewPass", dcNewPass);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void changeEmail(string dcUsername, string userEmail, string userNewEmail)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "update DevelopingCountryUser set userEmail = @userNewEmail where dcUsername = @dcUsername";
                comm.Parameters.AddWithValue("@userEmail", userEmail);
                comm.Parameters.AddWithValue("@userNewEmail", userNewEmail);
                comm.Parameters.AddWithValue("@dcUsername", dcUsername);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}